/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package controller;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import supermarketv0_class.*;
import java.sql.ResultSet;
import java.sql.Connection;
/**
 *
 * @author ΠΑΝΑΓΙΩΤΗΣ v1
 */
public class Apk {
   
    private String database = "SuperMarket"; //Ονομα βάσης
    private String user = "userAll"; //username βάσης
    private String password = "pass"; //password βάσης
    private String driver = "org.apache.derby.jdbc.ClientDriver"; //driver
    private String connString = "jdbc:derby://localhost:1527/"; //connection string
    private Connection connection;//εδώ θα γίνει η σύνδεση με τη βάση
    Customer customer;
    Bank bank;
    int id ;
    CreditCardAuthority cca;
 ArrayList b = new ArrayList();
 ArrayList c = new ArrayList();
   
 public Apk() {
        try {
            // load database driver class
            Class.forName(driver);
            // connect to database
            connection = DriverManager.getConnection(connString + database, user, password);
            System.out.println(connection);
        } catch (Exception ex) {
            System.out.println("Exception: " + ex);
        }
    }

    public void newRecord(int bankcode, String carnum, String fullname, int cvv) {
        String sql;
       // int id=0;
        int x=0;
        try {
            //ΕΥΡΕΣΗ ΤΟΥ ΜΕΓΙΣΤΟΥ(ΤΕΛΕΥΤΑΙΟΥ)ID
            ResultSet rs = connection.createStatement().executeQuery("SELECT MAX(CCA_ID) FROM USERALL.CREDIT_CARD_AUTHORITY");
            
            while (rs.next()) {
                
                id = rs.getInt(1) + 1;//ΑΥΞΗΣΗ ΤΟΥ ID ΚΑΤΑ ΕΝΑ
                System.out.println("Exc1" + id+" maxid " +rs.getInt(1));
            }
           if (id==8){
            id=9;
        }
               
           
        } catch (SQLException ex) {
            System.out.println("Exception:" + ex+ id+" maxid " +x );
        }
        
        sql = "INSERT INTO USERALL.CREDIT_CARD_AUTHORITY "
                + "(CCA_ID, CARDNUM, BANKCODE,FULLNAME,CVV) "
                + "VALUES "+ " (" + id + ",'"
                + carnum + "',"
                + bankcode+ ",'"
                + fullname + "',"
                + cvv + ")";
        try {
            //EKTELESE sql
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Exception: " + ex+" maxid " +x);
        }
    }

    public ArrayList readBanks() {
        
      try {
          
            
    ResultSet  rs = connection.createStatement().executeQuery("SELECT * FROM USERALL.BANK");
  
            while (rs.next()) {
              bank=new Bank(rs.getInt(1),rs.getString(2));//δημιουργία αντικειμένου
                b.add(bank);

                
            }
       } catch (SQLException ex) {
         System.out.println("Exception: " + ex);
     }

      return b;//ΕΠΙΣΤΡΟΦΗ
    }
    
    public int getRecord(int cvv){
         try {
            ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM USERALL.CREDIT_CARD_AUTHORITY WHERE CVV="+cvv+"");
           
            while (rs.next()) {
              cca=new CreditCardAuthority(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getInt(5));//δημιουργία αντικειμένου
               return cca.getCvv() ;

                
            }
        } catch (SQLException ex) {
            System.out.println("Exception: " + ex);
        }

      return 0;//ΕΠΙΣΤΡΟΦΗ
    }
    
    public ArrayList CreditCard(){
         try {
          
            
    ResultSet  rs = connection.createStatement().executeQuery("SELECT * FROM USERALL.CREDIT_CARD_AUTHORITY");
  
            while (rs.next()) {
              cca=new CreditCardAuthority(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getInt(5));//δημιουργία αντικειμένου
                c.add(cca);

                
            }
       } catch (SQLException ex) {
         System.out.println("Exception: " + ex);
     }

      return b;//ΕΠΙΣΤΡΟΦΗ
    }
    
     
}
